library(tidyverse)
library(data.table)
library(dtplyr)
library(lubridate)
library(stringr)
library(zoo)
library(here)
library(sassy)
library(RPostgres)

# Open the log
lf <- log_open(file.path(here("output", "log"), "log_CRSPmonthly.log"),
               autolog = T, show_notes = F)


# Send code to the log
log_code()

# 1 Read data from WRDS -------------------------------------------------------------------
sep("1 Read data from WRDS")

wrds <- dbConnect(Postgres(),
                  host='wrds-pgdata.wharton.upenn.edu',
                  port=9737,
                  dbname='wrds',
                  sslmode='require',
                  user='...').  # input your username on WRDS


SQL_statement <- 
  "SELECT a.permno, a.permco, a.date, a.ret, a.retx, a.vol,
          a.shrout, a.prc, a.cfacshr, a.bidlo, a.askhi,
          b.shrcd, b.exchcd, b.siccd, b.ticker, b.shrcls, 
          c.dlstcd, c.dlret                               
   FROM crsp.msf as a
   LEFT JOIN crsp.msenames as b
    ON a.permno=b.permno AND b.namedt<=a.date AND a.date<=b.nameendt
   LEFT JOIN crsp.msedelist as c
    ON a.permno=c.permno AND date_trunc('month', a.date) = date_trunc('month', c.dlstdt)"

res <- dbSendQuery(conn = wrds, statement = SQL_statement)
df_monthlyCRSP <- dbFetch(res)
dbClearResult(res)

# 2 Cleaning -------------------------------------------------------------------
sep("2 Cleaning")

# Make 2 digit SIC
df_monthlyCRSP <- df_monthlyCRSP %>%
  rename(sicCRSP = siccd) %>%
  mutate(sicCRSP = as.character(sicCRSP),
         sic2D = substr(sicCRSP, 1, 2),
         sic2D = as.numeric(sic2D))

# Create monthly date
df_monthlyCRSP <- df_monthlyCRSP %>%
  mutate(time_avail_m = floor_date(as.Date(date, origin = "1960-01-01"), "month")) %>%
  select(-date)

# Incorporate delisting return
df_monthlyCRSP <- df_monthlyCRSP %>%
  mutate(dlret = case_when(
    is.na(dlret) & (dlstcd == 500 | (dlstcd >= 520 & dlstcd <= 584)) & (exchcd == 1 | exchcd == 2) ~ -0.35,
    is.na(dlret) & (dlstcd == 500 | (dlstcd >= 520 & dlstcd <= 584)) & exchcd == 3 ~ -0.55,
    dlret < -1 & !is.na(dlret) ~ -1,
    is.na(dlret) ~ 0,
    TRUE ~ dlret
  ))

# Update return values
df_monthlyCRSP <- df_monthlyCRSP %>%
  mutate(ret = if_else(!is.na(ret), (1 + ret) * (1 + dlret) - 1, dlret),
         ret = if_else(is.na(ret) & dlret != 0, dlret, ret))

# Compute market value of equity
df_monthlyCRSP <- df_monthlyCRSP %>%
  mutate(shrout = shrout / 1000,
         vol = vol / 10^4,
         mve_c = shrout * abs(prc))

# Housekeeping
df_monthlyCRSP <- df_monthlyCRSP %>%
  select(-dlret, -dlstcd, -permco) 

# save
fwrite(df_monthlyCRSP, here("data", "CRSP", "monthlyCRSP.csv"))
saveRDS(df_monthlyCRSP, here("data", "CRSP", "monthlyCRSP.RDS"))

# Close log
log_close()

# View results
writeLines(readLines(lf))
